USE OILManagementStore1;
GO

IF OBJECT_ID('Disable_Log', 'P') IS NOT NULL
	DROP PROCEDURE Disable_Log;
GO

CREATE PROCEDURE Disable_Log
	@tableName NVARCHAR(30)
AS
	IF OBJECT_ID('TG_TRACK_' + @tableName, 'TR') IS NOT NULL
	    EXEC ('DROP TRIGGER TG_TRACK_' + @tableName + ';');
GO

IF OBJECT_ID('Enable_Log', 'P') IS NOT NULL
	DROP PROCEDURE Enable_Log;
GO

CREATE PROCEDURE Enable_Log
	@tableName NVARCHAR(30)
AS
	EXEC Disable_Log @tableName;	
	EXEC (
	'
	CREATE TRIGGER TG_TRACK_'+@tableName+'
	ON '+@tableName+'
	FOR  INSERT, UPDATE, DELETE
	AS
		DECLARE Cur CURSOR  
		FOR
		    SELECT ID
		    FROM   DELETED 
		    UNION
		
		SELECT ID
		FROM   INSERTED;		
		DECLARE @rowid UNIQUEIDENTIFIER; 
		
		OPEN Cur; 
		FETCH NEXT FROM Cur INTO @rowid;
		WHILE @@FETCH_STATUS = 0
		BEGIN
		    INSERT INTO HISTORY
		    VALUES
		      (
		        '''+@tableName+''',
		        @rowid,
		        GETDATE()
		      ); 
		    
		    FETCH NEXT FROM Cur INTO @rowid;
		END 
		
		CLOSE Cur; 
		DEALLOCATE Cur;	
	'
	)
GO

IF OBJECT_ID('UnprotectTable', 'P') IS NOT NULL
	DROP PROCEDURE UnprotectTable;
GO

CREATE PROCEDURE UnprotectTable
	@tableName NVARCHAR(30)
AS
	IF OBJECT_ID('TG_PROTECT_' + @tableName, 'TR') IS NOT NULL
	    EXEC ('DROP TRIGGER TG_PROTECT_' + @tableName + ';');
GO

IF OBJECT_ID('ProtectTable', 'P') IS NOT NULL
	DROP PROCEDURE ProtectTable;
GO

CREATE PROCEDURE ProtectTable
	@tableName NVARCHAR(30)
AS
	EXEC UnprotectTable @tableName;
	EXEC (
	    '
	    CREATE TRIGGER TG_PROTECT_' + @tableName + '
	    ON ' + @tableName + '
	    FOR  INSERT, UPDATE, DELETE
	    AS
	    	RAISERROR(''Cannot change protected TABLE'', 16, 1); 
	    	ROLLBACK TRANSACTION;
	'
	)
GO

IF OBJECT_ID('GetDeletedRows', 'P') IS NOT NULL
	DROP PROCEDURE GetDeletedRows;
GO

CREATE PROCEDURE GetDeletedRows	
	@tableName NVARCHAR(30),
	@from DATETIME
AS			  		   
	EXEC (
	'	 
	 SELECT DISTINCT tablerow AS DeteledRowID
	 FROM   HISTORY
	 WHERE  TableName = ''' + @tableName + '''
	        AND ChangeTime >= ''' + @from + '''
	        AND tableRow NOT IN (SELECT ID
	                             FROM   '+@tableName+')
	' 	                     
	)
GO

IF OBJECT_ID('GetChangedRows', 'P') IS NOT NULL
	DROP PROCEDURE GetChangedRows;
GO

CREATE PROCEDURE GetChangedRows	
	@tableName NVARCHAR(30),
	@from DATETIME
AS					  		  
	EXEC (
	'	 
	 SELECT *
	 FROM   ' + @tableName + '
	 WHERE  ID IN (SELECT TableRow
	               FROM   HISTORY
	               WHERE  TableName = ''' + @tableName + '''
	                      AND ChangeTime >= ''' + @from + ''')
	'	 
	)
GO

IF OBJECT_ID('GetHistoryRowBefore', 'P') IS NOT NULL
	DROP PROCEDURE GetHistoryRowBefore;
GO

CREATE PROCEDURE GetHistoryRowBefore
	@beforeTime DATETIME
AS
	SELECT MAX(ID) AS ID
	FROM HISTORY
	WHERE ChangeTime <= @beforeTime;
GO

CREATE PROCEDURE AddUser
	@UserName NVARCHAR(30),
	@PassWord	NVARCHAR(30),
	@SysAdmin INT = 0
AS
BEGIN
	IF SUSER_SID(@UserName) IS NULL
		EXEC('CREATE LOGIN ['+@UserName+'] WITH PASSWORD = '''+@PassWord+'''')
	ELSE
		EXEC('ALTER LOGIN ['+@UserName+'] WITH PASSWORD = '''+@PassWord+'''')
	
	IF @@ERROR <>0
		RETURN
	
	EXEC('CREATE USER ['+@UserName+'] FOR LOGIN ['+@UserName+'] WITH DEFAULT_SCHEMA = dbo');
	
	EXEC sys.sp_addrolemember @rolename=N'db_owner', @membername=@UserName;
	IF @SysAdmin <> 0
		EXEC sys.sp_addsrvrolemember @loginame = @UserName, @rolename = N'sysadmin';
END	
GO

CREATE PROCEDURE RemoveUser
	@UserName NVARCHAR(30)
AS
BEGIN
	EXEC('DROP LOGIN ['+@UserName+']');
	IF @@ERROR = 0
		EXEC('DROP USER ['+@UserName+']');		
END
GO

CREATE PROCEDURE ChangePassword
	@OldPassWord NVARCHAR(30),
	@NewPassWord NVARCHAR(30)
AS
BEGIN
	EXEC sp_Password @OldPassWord, @NewPassWord
END
GO

CREATE PROCEDURE GetUserList
AS
	EXEC sp_helprolemember 'db_owner';
GO


EXEC Enable_Log 'Companies';
GO
EXEC Enable_Log 'Customers';
GO
EXEC Enable_Log 'Expenses';
GO
EXEC Enable_Log 'Imports';
GO
EXEC Enable_Log 'Sells';
GO
EXEC Enable_Log 'Products';
GO
EXEC Enable_Log 'Payments';
GO
EXEC Enable_Log 'Stores';
GO
EXEC Enable_Log 'Debts';
GO
